package com.dp.appi.dao.impl;

import com.dp.appi.dao.PTDao;
import com.dp.appi.exception.CommonException;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

/**
 * Created by bchen on 2016/5/17.
 */
@Component
public class PTDaoImpl extends BaseDaoImpl implements PTDao {
    private static final String TABLE_SCHEMA = "jgzspass";

    /**
     * 获取PT字段
     *
     * @param pt_id
     * @return
     */
    @Override
    public List<Map<String, Object>> ptField(int pt_id) {
        StringBuffer sql = new StringBuffer();
        sql.append("select COLUMN_NAME,DATA_TYPE ");
        sql.append("from information_schema.COLUMNS ");
        sql.append("where table_name =  ");
        sql.append("(select pt_table from p_pt where pt_id = ?) ");
        sql.append("and table_schema = ?");
        List<Map<String, Object>> ptFieldList = getJdbcTemplate().queryForList(sql.toString(), pt_id, TABLE_SCHEMA);
        return ptFieldList;
    }

    /**
     * 获取PT表
     *
     * @param pt_id
     * @return
     */
    @Override
    public String ptTable(int pt_id) {
        StringBuffer sql = new StringBuffer();
        sql.append("select pt_table  ");
        sql.append("from p_pt where pt_id = ? ");
        String ptTable = queryForObject(sql.toString(), String.class, pt_id);
        return ptTable;
    }

    /**
     * 查询某条定位点扩展信息
     *
     * @param pt_table
     * @param pt_objectid
     * @return
     */
    @Override
    public Map<String, Object> queryPt(String pt_table, Long pt_objectid) {
        StringBuffer sql = new StringBuffer();
        sql.append("select *  from ");
        sql.append(pt_table);
        sql.append(" where id = ? ");
        Map<String, Object> ptMap = queryForMap(sql.toString(), pt_objectid);
        return ptMap;
    }

    /**
     * 新增定位点扩展信息
     *
     * @param ptMapList
     */
    @Override
    public Long newPt(final List<Map<String, Object>> ptMapList, String pt_table) {

        String columns = "";
        String question = "";
        Map<String, Object> paramMap = new HashMap<>();
        for (Map<String, Object> ptMap : ptMapList) {
            columns += ptMap.get("column_name");
            columns += ",";
            question += ":" + ptMap.get("column_name") + ",";
            paramMap.put(String.valueOf(ptMap.get("column_name")), ptMap.get("column_value"));
        }
        columns = columns.contains(",") ? columns.substring(0, columns.lastIndexOf(",")) : columns;
        question = question.contains(",") ? question.substring(0, question.lastIndexOf(",")) : question;

        String sql = "insert into " + pt_table + "(" + columns + ") values(" + question + ")";

        Long id = executeSqlReturnKey(sql, paramMap);

        return id;
    }

    /**
     * 修改定位点扩展信息
     *
     * @param ptMapList
     * @param pt_table
     * @param pt_objectid
     */
    @Override
    public void upPt(List<Map<String, Object>> ptMapList, String pt_table, Long pt_objectid) {
        if (null == ptMapList || ptMapList.size() == 0) {
            return;
        }
        StringBuffer sql = new StringBuffer();
        sql.append(" UPDATE ");
        sql.append(pt_table);
        sql.append(" SET ");

        List paramList = new ArrayList();
        for (int i = 0; null != ptMapList && i < ptMapList.size(); i++) {
            Map<String, Object> ptMap = ptMapList.get(i);

            Object a = ptMap.get("column_value");
            String value = String.valueOf(ptMap.get("column_value"));

            if (!value.equals("")) {
                if (i != 0)
                    sql.append(" , ");
                sql.append(ptMap.get("column_name"));
                sql.append(" = ? ");
                paramList.add(ptMap.get("column_value"));
            }
        }

        sql.append(" where id = ? ");
        paramList.add(pt_objectid);
        getJdbcTemplate().update(sql.toString(), paramList.toArray());
    }

    /**
     * 删除定位点扩展扩展信息
     *
     * @param pt_table
     * @param pt_objectid
     */
    @Override
    public void delPt(String pt_table, Long pt_objectid) {
        StringBuffer sql = new StringBuffer();
        sql.append(" DELETE FROM ");
        sql.append(pt_table);
        sql.append(" where id = ?");
        getJdbcTemplate().update(sql.toString(), pt_objectid);
    }

    /**
     * 更新定位对象id
     *
     * @param id
     * @param exploration_id
     */
    @Override
    public void upPtObjectid(Long id, Long exploration_id) {
        StringBuffer upSql = new StringBuffer();
        upSql.append("UPDATE p_exploration SET pt_objectid = ? where exploration_id = ?;");
        getJdbcTemplate().update(upSql.toString(), id, exploration_id);
    }

    /**
     * 根据数据类型设置ps
     *
     * @param ps
     * @param i
     * @param dataType
     */
    private void setPs(PreparedStatement ps, int i, String dataType, String value) throws SQLException {
        if (null == value) {
            ps.setNull(i, java.sql.Types.NULL);
        }
        if ("bigint".equals(dataType)) {
            ps.setLong(i, Long.parseLong(value));
        } else if ("varchar".equals(dataType)) {
            ps.setString(i, value);
        } else if ("int".equals(dataType)) {
            ps.setInt(i, Integer.valueOf(value));
        } else if ("tinyint".equals(dataType)) {
            ps.setInt(i, Integer.valueOf(value));
        } else if ("decimal".equals(dataType)) {
            ps.setBigDecimal(i, BigDecimal.valueOf(Double.valueOf(value)));
        } else if ("float".equals(dataType)) {
            ps.setFloat(i, Float.valueOf(value));
        }
    }
}
